package com.epam.employeebase.dao.jdbc;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Set;

import org.apache.log4j.Level;
import org.apache.log4j.Logger;

import com.epam.employeebase.dao.IEmployeeDAO;
import com.epam.employeebase.dao.exception.DAOException;
import com.epam.employeebase.dao.jdbc.dbcp.ConnectionPool;
import com.epam.employeebase.dao.jdbc.dbcp.exception.PoolException;
import com.epam.employeebase.entity.Address;
import com.epam.employeebase.entity.City;
import com.epam.employeebase.entity.Company;
import com.epam.employeebase.entity.Country;
import com.epam.employeebase.entity.Employee;
import com.epam.employeebase.entity.Employment;
import com.epam.employeebase.entity.Office;
import com.epam.employeebase.entity.Position;

public final class JDBCEmployeeDAO implements IEmployeeDAO {
	private static final Logger logger = Logger.getLogger(JDBCEmployeeDAO.class);
	private ConnectionPool connectionPool = ConnectionPool.getInstance();
	private static final String EMPLOYEE_COUNT_SQL_QUERY = "SELECT count(*) FROM employees";
	private static final String EMPLOYEE_LIST_SQL_QUERY = "SELECT employeeId, employee.first_name AS firstName, employment.id AS employmentId," +
		"employee.last_name AS lastName, employeeCountry.name AS employeeCountryName, employeeCity.name AS employeeCityName, employeeAddress.street AS employeeStreet,"+
		"employeeAddress.house AS employeeHouse, employeeAddress.apartment AS employeeApartment,company.name AS companyName, officeCountry.name AS officeCountryName, officeCity.name AS officeCityName,"+
		"officeAddress.street AS officeStreet, officeAddress.house AS officeHouse, officeAddress.apartment AS officeApartment, position.name AS positionName,"+
		"(SELECT COUNT(DISTINCT e.employee_id) FROM employments e WHERE e.office_id = office.id) AS officeEmployeeNumber"+		
		" FROM (SELECT employeeId FROM (SELECT id employeeId, row_number() OVER (ORDER BY id) AS rowNumber FROM employees) WHERE rowNumber <= %d  AND rowNumber > %d)"+
		" LEFT JOIN employees employee ON employeeId = employee.id LEFT JOIN addresses employeeAddress ON employee.address_id = employeeAddress.id"+
		" LEFT JOIN cities employeeCity ON employeeAddress.city_id = employeeCity.id LEFT JOIN countries employeeCountry ON employeeCity.country_id = employeeCountry.id"+
		" LEFT JOIN employments employment ON employee.id = employment.employee_id LEFT JOIN offices office ON employment.office_id = office.id"+
		" LEFT JOIN companies company ON office.company_id = company.id LEFT JOIN addresses officeAddress ON office.address_id = officeAddress.id"+
		" LEFT JOIN cities officeCity ON officeAddress.city_id = officeCity.id LEFT JOIN countries officeCountry ON officeCity.country_id = officeCountry.id"+
		" LEFT JOIN positions position ON employment.position_id = position.id ORDER BY employeeId";
	@Override
	public List<Employee> list(int firstEntry, int entriesNumber) throws DAOException {
		Connection connection = null;
		Statement statement = null;
		List<Employee> employees = new ArrayList<Employee>();
		try{
			connection = connectionPool.getConnection();
			statement = connection.createStatement();
			int lastEntry = firstEntry + entriesNumber;
			String query = String.format(EMPLOYEE_LIST_SQL_QUERY, lastEntry, firstEntry);
			ResultSet resultSet = statement.executeQuery(query);
			
			while(resultSet.next()){			
				Long employeeId = resultSet.getLong(ColumnNames.EMPLOYEE_ID);
				Employee employee = retrieveEmployee(employees, employeeId);
				if(employee == null){
					employee = new Employee();
					employee.setId(employeeId);
					Address employeeAddress =  new Address();
					City employeeCity = new City();
					Country employeeCountry = new Country();				
					employee.setId(resultSet.getLong(ColumnNames.EMPLOYEE_ID));
					employee.setFirstName(resultSet.getString(ColumnNames.EMPLOYEE_FIRSTNAME));
					employee.setLastName(resultSet.getString(ColumnNames.EMPLOYEE_LASTNAME));				
					employeeAddress.setStreet(resultSet.getString(ColumnNames.EMPLOYEE_STREET));
					employeeAddress.setHouse(resultSet.getInt(ColumnNames.EMPLOYEE_HOUSE));
					employeeAddress.setApartment(resultSet.getInt(ColumnNames.EMPLOYEE_APARTMENT));				
					employeeCity.setName(resultSet.getString(ColumnNames.EMPLOYEE_CITY));				
					employeeCountry.setName(resultSet.getString(ColumnNames.EMPLOYEE_COUNTRY));				
					employeeCity.setCountry(employeeCountry);
					employeeAddress.setCity(employeeCity);
					employee.setAddress(employeeAddress);
					
					employees.add(employee);
				}				
				Set<Employment> employments = employee.getEmployments();
				Long employmentId = resultSet.getLong(ColumnNames.EMPLOYMENT_ID);
				if(employmentId != 0){
					Employment employment = new Employment();
					Office office = new Office();
					Company company = new Company();
					Address officeAddress =  new Address();
					City officeCity = new City();
					Country officeCountry = new Country();
					Position position = new Position();
					
					officeAddress.setStreet(resultSet.getString(ColumnNames.OFFICE_STREET));
					officeAddress.setHouse(resultSet.getInt(ColumnNames.OFFICE_HOUSE));
					officeAddress.setApartment(resultSet.getInt(ColumnNames.OFFICE_APARTMENT));
					
					officeCity.setName(resultSet.getString(ColumnNames.OFFICE_CITY));
					
					officeCountry.setName(resultSet.getString(ColumnNames.OFFICE_COUNTRY));
					
					company.setName(resultSet.getString(ColumnNames.COMPANY_NAME));
					
					officeCity.setCountry(officeCountry);
					officeAddress.setCity(officeCity);			
					office.setEmployeeCount(resultSet.getInt(ColumnNames.OFFICE_EMPLOYEE_NUMBER));
					office.setCompany(company);
					office.setAddress(officeAddress);
					
					position.setName(resultSet.getString(ColumnNames.POSITION_NAME));
					
					employment.setOffice(office);
					employment.setPosition(position);
					employment.setEmployee(employee);
					employments.add(employment);
				}
			}
		} catch(SQLException | PoolException e){
			throw new DAOException(e);
		} finally{
			closeResources(statement, connection);
		}
		return employees;
	}
	@Override
	public long employeeCount() throws DAOException {
		Connection connection = null;
		Statement statement = null;
		long employeesNumber = 0;
		try{
			connection = connectionPool.getConnection();
			statement = connection.createStatement();
			ResultSet resultSet = statement.executeQuery(EMPLOYEE_COUNT_SQL_QUERY);
			while(resultSet.next()){
				employeesNumber = resultSet.getLong(1);
			}
		} catch(SQLException | PoolException e){
			throw new DAOException(e);
		} finally {
			closeResources(statement, connection);
		}
		return employeesNumber;
	}
	
	private Employee retrieveEmployee(List<Employee> employees, Long employeeId){
		for(Employee employee: employees){
			if(employeeId.equals(employee.getId())){
				return employee;
			}
		}
		return null;
	}
	private void closeResources(Statement statement, Connection connection){
		if(statement != null){
			try{
				statement.close();
			}catch(SQLException e){
				logger.log(Level.ERROR, "SQLException", e);
			}
		}
		if(connection != null){
			try{
				connection.close();
			}catch(SQLException e){
				logger.log(Level.ERROR, "SQLException", e);
			}
		}
	}

}
